import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.swing.filechooser.FileSystemView;
import java.io.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description: 导出学生考勤  // 类说明，在创建类时要填写
 * @ClassName: exportRate    // 类名，会自动填充
 * @Author: wangtai          // 创建者
 * @Date: 2021/3/3 11:09   // 时间
 * @Version: 1.0     // 版本
 */
public class exportRate  {
    public static void main(String[] args)  {
        String excelName = "\\2.xlsx";
        File desktopDir = FileSystemView.getFileSystemView() .getHomeDirectory();

        String desktopPath = desktopDir.getAbsolutePath()+excelName;
        System.out.println(desktopPath);
        try {
        File excel = new File(desktopPath);
        if (excel.isFile() && excel.exists()) {   //判断文件是否存在

            String[] split = excel.getName().split("\\.");  //.是特殊字符，需要转义！！！！！
            Workbook wb;
            //根据文件后缀（xls/xlsx）进行判断
            if ( "xls".equals(split[1])){
                FileInputStream fis = new FileInputStream(excel);   //文件流对象
                wb = new HSSFWorkbook(fis);
            }else if ("xlsx".equals(split[1])){
                wb = new XSSFWorkbook(excel);
            }else {
                System.out.println("文件类型错误!");
                return;
            }
            Sheet sheet = wb.getSheetAt(0);     //读取sheet 0

            int firstRowIndex = sheet.getFirstRowNum()+1;   //第一行是列名，所以不读
            int lastRowIndex = sheet.getLastRowNum();

            List<Student> oldStudents = new ArrayList<>();
            for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {   //遍历行

                Row row = sheet.getRow(rIndex);
                if (row != null) {
                    Student stu = new Student();
                    stu.setOnlyKey(row.getCell(1).toString());
                    stu.setClassName(row.getCell(5).toString());
                    oldStudents.add(stu);
                }
            }

            //java8 去重
            List<Student> newStudents = oldStudents.stream().collect(Collectors.collectingAndThen(
                    Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(Student::getOnlyKey))), ArrayList::new)
            );
            Map<String,List<Student>> map2 =newStudents.stream().collect(Collectors.groupingBy(Student::getClassName));

            List<ExportRateEntity> exList = new ArrayList<>();
            map2.entrySet().forEach(entry->{
                exList.add( orderByName(entry.getKey(),entry.getValue().stream().collect(Collectors.summingInt(Student::getNum))));
                //System.out.println(entry.getKey()+"---"+entry.getValue().stream().collect(Collectors.summingInt(Student::getNum)));
            });
            List<ExportRateEntity> newEList = exList.stream().sorted(Comparator.comparingInt(ExportRateEntity::getNO)).collect(Collectors.toList());
            newEList.forEach(exportRateEntity -> {
                System.out.println(exportRateEntity.getClassName()+"----"+exportRateEntity.getNum());
            });
            writeDataHubData(newEList,"test");
        } else {
            System.out.println("找不到指定的文件");
        }
    } catch (Exception e) {
        e.printStackTrace();
    }


    }

    private static ExportRateEntity orderByName(String key, Integer collect) {

        ExportRateEntity ere = new ExportRateEntity();
        switch (key){
            case "2018服装与服饰设计1班":
                ere.setNO(6);
                ere.setClassName("2018服装与服饰设计（普通）");
                ere.setNum(collect);

                break;
            case "2018服装与服饰设计(美术类)2班":
                ere.setNO(5);
                ere.setClassName("2018服装与服饰设计二班（美术）");
                ere.setNum(collect);
                break;
            case "2018服装陈列与展示设计1班":
                ere.setNO(10);
                ere.setClassName("2018服装陈列与展示设计");
                ere.setNum(collect);
                break;
            case "2018市场营销1班":
                ere.setNO(11);
                ere.setClassName("2018市场营销一班");
                ere.setNum(collect);
                break;
            case "2018服装设计与工艺1班":
                ere.setNO(7);
                ere.setClassName("2018服装设计与工艺");
                ere.setNum(collect);
                break;
            case "2018纺织品检验与贸易1班":
                ere.setNO(2);
                ere.setClassName("2018纺织品检验与贸易");
                ere.setNum(collect);
                break;
            case "2018市场营销2班":
                ere.setNO(12);
                ere.setClassName("2018市场营销二班");
                ere.setNum(collect);
                break;
            case "2018服装与服饰设计(美术类)1班":
                ere.setNO(4);
                ere.setClassName("2018服装与服饰设计一班（美术）");
                ere.setNum(collect);
                break;
            case "2018人物形象设计1班":
                ere.setNO(1);
                ere.setClassName("2018人物形象设计");
                ere.setNum(collect);
                break;
            case "2018服装设计与工艺(校企合作，鲁泰纺织)1班":
                ere.setNO(9);
                ere.setClassName("2018服装设计与工艺（鲁泰校企）");
                ere.setNum(collect);
                break;
            case "2018服装高级定制卓越专业1班":
                ere.setNO(8);
                ere.setClassName("2018服装设计与工艺（卓越）");
                ere.setNum(collect);
                break;
            case "2018现代纺织技术1班":
                ere.setNO(3);
                ere.setClassName("2018现代纺织技术");
                ere.setNum(collect);
                break;
            case "服装与服饰设计（三二转段）一班":
                ere.setNO(15);
                ere.setClassName("服装与服饰设计（三二转段）一班");
                ere.setNum(collect);
                break;

        }
        return ere;
    }

    public static boolean writeDataHubData(List<ExportRateEntity> result, String fileName) throws UnsupportedEncodingException {
        fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
               long start = System.currentTimeMillis();
              String filePath = "D:\\software\\test";
               StringBuilder content = new StringBuilder();
              boolean flag = false;
               BufferedWriter out = null;
              try {
                      if (result != null && !result.isEmpty() ) {
                               fileName += "_" + System.currentTimeMillis() + ".txt";
                              File pathFile = new File(filePath);
                            if (!pathFile.exists()) {
                                      pathFile.mkdirs();
                                   }
                              String relFilePath = filePath + File.separator + fileName;
                                File file = new File(relFilePath);
                               if (!file.exists()) {
                                       file.createNewFile();
                                  }
                               out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "UTF-8"));
               //                //标题头
               //                out.write("curr_time,link_id,travel_time,speed,reliabilitycode,link_len,adcode,time_stamp,state,public_rec_time,ds");
                //                out.newLine();

                                 for (ExportRateEntity info : result) {

                                         out.write(info.getNum());
                                         out.newLine();
                                   }
                               flag = true;
                 //                logger.info("写入文件耗时：*********************************" + (System.currentTimeMillis() - start) + "毫秒");
                                 System.out.println("写入文件耗时：*********************************" + (System.currentTimeMillis() - start) + "毫秒");
                             }
                     } catch (IOException e) {
                        e.printStackTrace();
                    } finally {
                       if (out != null) {
                                try {
                                        out.flush();
                                        out.close();
                                   } catch (IOException e) {
                                        e.printStackTrace();
                                    }
                          }
                        return flag;
                   }
            }
}
